﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Inventory
{
    public class ProductVariantManager:ConnectionManager
    {
        #region Adding/Variant
        
        public void AddVariant(string _BrandID, string _CategoryCode, string _ProductCode, string _VariantCode, string _VariantName)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO M_BRND_CT_PRD_VRNT (BrandID, CatgryCode, ProdCode, VrntCode, VrntName) VALUES (@BrandID, @CatgryCode, @ProdCode, @VrntCode, @VrntName)";
            oCommand.Parameters.Add(new SqlParameter("@BrandID", _BrandID));
            oCommand.Parameters.Add(new SqlParameter("@CatgryCode", _CategoryCode));
            oCommand.Parameters.Add(new SqlParameter("@ProdCode", _ProductCode));
            oCommand.Parameters.Add(new SqlParameter("@VrntCode", _VariantCode));
            oCommand.Parameters.Add(new SqlParameter("@VrntName", _VariantName));

            oCommand.ExecuteNonQuery();
        }

        public void UpdateVariant(string _BrandID, string _CategoryCode, string _ProductCode, string _VariantCode, string _VariantName)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE M_BRND_CT_PRD_VRNT SET  VrntName=@VrntName WHERE BrandID=@BrandID AND CatgryCode=@CatgryCode AND ProdCode=@ProdCode AND VrntCode=@VrntCode";
            
            oCommand.Parameters.Add(new SqlParameter("@BrandID", _BrandID));
            oCommand.Parameters.Add(new SqlParameter("@CatgryCode", _CategoryCode));
            oCommand.Parameters.Add(new SqlParameter("@ProdCode", _ProductCode));
            oCommand.Parameters.Add(new SqlParameter("@VrntCode", _VariantCode));
            oCommand.Parameters.Add(new SqlParameter("@VrntName", _VariantName));

            oCommand.ExecuteNonQuery();
        }

        #endregion

        public DataTable GetVariants(string _BrandID, string _CatgryCode, string _ProductCode)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM M_BRND_CT_PRD_VRNT WHERE BrandID=@BrandID AND CatgryCode=@CatgryCode AND ProdCode=@ProdCode ";
            oCommand.Parameters.Add(new SqlParameter("@BrandID", _BrandID));
            oCommand.Parameters.Add(new SqlParameter("@CatgryCode", _CatgryCode));
            oCommand.Parameters.Add(new SqlParameter("@ProdCode", _ProductCode));
            

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }


        public DataTable GetVariantByName(string _BrandID, string _CatgryCode, string _ProductCode, string _VariantName)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            _VariantName = '%' + _VariantName + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM M_BRND_CT_PRD_VRNT WHERE BrandID=@BrandID AND CatgryCode=@CatgryCode AND ProdCode=@ProdCode and VrntName Like @VariantName";
            oCommand.Parameters.Add(new SqlParameter("@BrandID", _BrandID));
            oCommand.Parameters.Add(new SqlParameter("@CatgryCode", _CatgryCode));
            oCommand.Parameters.Add(new SqlParameter("@ProdCode", _ProductCode));
            oCommand.Parameters.Add(new SqlParameter("@VariantName", _VariantName));
            

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
    }
}
